SQL (Structured Query Language) Best Practices

SQL Format

Capitalize all letters in SQL keywords such as SELECT,JOIN or WHERE.
String (text) variables in SQL statements should have brackets around them and then single quotes around them.
Integer variables in SQL statements should have brackets around them without quotes.
Avoid using SQL inside Math Loops.

SQLs needed in MathLoops should be handled outside the loop.
SQLs may fill arrays and then the arrays should be indexed inside the loop for manipulation.

Consider whether the query could return one or more than one result and configure it appropriately.

Use functions to ensure one value is returned when appropriate. Functions such as SUM, MIN, MAX, etc., ensure the return of one value.
SQL that is not used to fill a combo box field should return one value for a MathVariable.
An exception to returning one value can be made when performing a COLLECTION.

COLLECTION SQL should return two elements: Name and Value with multiple rows. When SQL is used to fill a field’s combo box, the SQL should return two columns and multiple rows.
In a COLLECTION if the KEY is the same name as a field name, then concatenate a string to the first column returned in the COLLECTION SQL.

If possible, avoid calling the FIELD and COLLECTION KEY the same name.

SQL that is used to fill a field combo box should return two values.

One value for the Option Value and the other value for the Text Value.

When possible, subqueries should be avoided and JOINs should be used for better performance.
Organize table names used in SQLs with the selection criteria of that table. The FROM table will be last using WHERE, all other tables (through JOINs) should use AND.
Only one table name should be after FROM. The remaining tables will be part of the SQL through JOIN.
When using ThisPendingActivityXML, use the following configuration to help performance:

<MathVariable VARIABLENAME="ThisPendingActivityXML" TYPE="SQL">SELECT XMLData FROM AsActivity WHERE ActivityGUID = '[Activity:ActivityGUID]'</MathVariable>.

Use ThisPendingActivityXML as needed then do:

<MathVariable VARIABLENAME="ThisPendingActivityXML" TYPE="VALUE"></MathVariable>. This configuration will stop writing too much data to the database. In the extreme circumstances that you do need to load up a large amount of XML into a variable, this will clear out the variable so it does not write as much data to AsActivity XMLData.

Retrieving Data

Use SQL to retrieve data from the database.

Do not use SQL to manipulate dates.
Do not use the SQL CASE statement.

Don’t JOIN tables unnecessarily.

For example, AsActivity contains PolicyGUID, therefore there is no need to JOIN AsPolicy to retrieve PolicyGUID.

When performing SQL to select one or more AsActivity records, be sure to consider AsActivity.StatusCode and AsActivity.TypeCode values to include/exclude records as desired.

Avoid returning pending, shadow, undo, redo, etc., records that are not needed.
For AsActivity.StatusCode,

For active use 01 and 14 together.
For pending use 02, 09 and 13 together.

When querying for the most recent transaction of a certain type, query by EffectiveDate DESC, ProcessingOrder DESC, ActivityGMT DESC and then fetch first row only. (Also see #1 above).
If a piece of information is already defined as a field, don’t use a SQL to retrieve this information again from the database. Since this value is already a <Field>, change to TYPE=”FIELD” and reference the field name.
Use PLANFIELD, POLICYFIELD, or SEGMENTFIELD when retrieving information from AsPlanField, policy information, or a segment.

For SEGMENTFIELD retrieval, if more than one field is to be returned, and not in a segment loop, then query the SegmentGUID and insert into an array.
Initialize a MathVariable to hold the array's index value.
With these two variables, you can now use SEGMENTFIELD.

Example XML

<MathVariable VARIABLENAME="SegmentGUIDArray" TYPE="NUMERICARRAY" OPERATION="FILLBY-SQL">Write SQL to retrieve the SegmentGUID that you would like to retrieve a Segment Field</MathVariable>

<MathVariable VARIABLENAME="SegmentFieldRetrieved" TYPE="SEGMENTFIELD" SOURCEARRAY="SegmentGUIDArray">Name of the Field you would like to retrieve</MathVariable>

In fields, when defining a combo box, use SQL queries calling ASCODE rather than redefining as fixed queries in business rules when possible. This will help with standardization and maintenance.

Information may need to be added to AsCode and must be cleared with a Configuration Lead.

Use SQL as little as possible. Use COLLECTION to bring back as much usable data as possible in one round trip to the database. Use CollectionValue to parse the individual pieces of data from the COLLECTION.